package cn.iocoder.yudao.module.product.evaluation.dal.mysql;

import cn.hutool.core.util.ObjectUtil;
import cn.iocoder.yudao.framework.business.basic.dao.mapper.CoreMapper;
import cn.iocoder.yudao.framework.business.basic.dao.query.CoreLambdaQueryWrapper;
import cn.iocoder.yudao.framework.business.evaluation.enums.ScoreTypeEnum;
import cn.iocoder.yudao.framework.common.pojo.PageParam;
import cn.iocoder.yudao.framework.common.pojo.PageResult;
import cn.iocoder.yudao.module.product.evaluation.bo.ShopIdCommentCountBO;
import cn.iocoder.yudao.module.product.evaluation.bo.ShopIdScoreBO;
import cn.iocoder.yudao.module.product.evaluation.bo.SpuIdScoreBO;
import cn.iocoder.yudao.module.product.evaluation.dal.dataobject.ProductEvaluationDO;
import cn.iocoder.yudao.module.product.evaluation.vo.AppProductEvaluationPageReqVO;
import cn.iocoder.yudao.module.product.evaluation.vo.ProductEvaluationPageReqVO;
import jakarta.annotation.Nullable;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Collection;
import java.util.List;

@Mapper
public interface ProductEvaluationMapper extends CoreMapper<ProductEvaluationDO> {

    default PageResult<ProductEvaluationDO> selectPage(ProductEvaluationPageReqVO reqVO) {
        return selectPage(reqVO, new CoreLambdaQueryWrapper<ProductEvaluationDO>()
                .eqIfPresent(ProductEvaluationDO::getOrderId, reqVO.getOrderId())
                .eqIfPresent(ProductEvaluationDO::getSpuId, reqVO.getSpuId())
                .eqIfPresent(ProductEvaluationDO::getScores, reqVO.getScores())
                .eqIfPresent(ProductEvaluationDO::getReplyStatus, reqVO.getReplyStatus())
                .betweenIfPresent(ProductEvaluationDO::getCreateTime, reqVO.getCreateTime())
                .likeIfPresent(ProductEvaluationDO::getSpuName, reqVO.getSpuName())
                .orderByDesc(ProductEvaluationDO::getId));
    }

    static void appendTabQuery(CoreLambdaQueryWrapper<ProductEvaluationDO> queryWrapper, Integer type) {
        // 构建好评查询语句：好评计算 总评 >= 4
        if (ObjectUtil.equal(type, ScoreTypeEnum.POSITIVE.getValue())) {
            queryWrapper.ge(ProductEvaluationDO::getScores, 4);
        }
        // 构建中评查询语句：中评计算 总评 >= 3 且 总评 < 4
        if (ObjectUtil.equal(type, ScoreTypeEnum.NEUTER.getValue())) {
            queryWrapper.ge(ProductEvaluationDO::getScores, 3);
            queryWrapper.lt(ProductEvaluationDO::getScores, 4);
        }
        // 构建差评查询语句：差评计算 总评 < 3
        if (ObjectUtil.equal(type, ScoreTypeEnum.NEGATIVE.getValue())) {
            queryWrapper.lt(ProductEvaluationDO::getScores, 3);
        }
        // 构建有图查询语句
        if (ObjectUtil.equal(type, ScoreTypeEnum.HAS_PICTURE.getValue())) {
            queryWrapper.isNotNull(ProductEvaluationDO::getPicUrls);
        }
    }

    default PageResult<ProductEvaluationDO> selectPage(AppProductEvaluationPageReqVO reqVO, Boolean visible) {
        CoreLambdaQueryWrapper<ProductEvaluationDO> queryWrapper = new CoreLambdaQueryWrapper<>();
        queryWrapper.eqIfPresent(ProductEvaluationDO::getShopId, reqVO.getShopId());
        queryWrapper.eqIfPresent(ProductEvaluationDO::getSpuId, reqVO.getSpuId());
        queryWrapper.eqIfPresent(ProductEvaluationDO::getVisible, visible);
        // 构建评价查询语句
        appendTabQuery(queryWrapper, reqVO.getType());
        // 按评价时间排序最新的显示在前面
        queryWrapper.orderByDesc(ProductEvaluationDO::getCreateTime);
        return selectPage(reqVO, queryWrapper);
    }


    default PageResult<ProductEvaluationDO> selectPage(Long userId, PageParam reqVO) {
        CoreLambdaQueryWrapper<ProductEvaluationDO> queryWrapper = new CoreLambdaQueryWrapper<>();
        queryWrapper.eqIfPresent(ProductEvaluationDO::getUserId, userId);
        // 按订单分组
        queryWrapper.groupBy(ProductEvaluationDO::getOrderId);
        // 按评价时间排序，最新的显示在前面
        queryWrapper.orderByDesc(ProductEvaluationDO::getCreateTime);
        return selectPage(reqVO, queryWrapper);
    }

    default ProductEvaluationDO selectByUserIdAndId(Long userId, Long id) {
        return selectOne(ProductEvaluationDO::getUserId, userId, ProductEvaluationDO::getId, id);
    }

    default ProductEvaluationDO selectByUserIdAndOrderId(Long userId, Long orderId) {
        return selectOne(ProductEvaluationDO::getUserId, userId, ProductEvaluationDO::getOrderId, orderId);
    }

    default ProductEvaluationDO selectByUserIdAndOrderItemId(Long userId, Long orderItemId) {
        return selectOne(ProductEvaluationDO::getUserId, userId, ProductEvaluationDO::getOrderItemId, orderItemId);
    }

    @Select("""
            SELECT IFNULL(AVG(scores), 5.0) AS score FROM product_comment WHERE deleted = FALSE AND shop_id = #{shopId}
            """)
    float avgScoreByShopId(@Param("shopId") Long shopId);

    @Select("""
            <script>
            SELECT shop_id, IFNULL(AVG(scores), 5.0) AS score
            FROM product_comment
            WHERE deleted = FALSE AND shop_id IN
            <foreach item="id" collection="shopIds" open="(" separator="," close=")">
                #{id}
            </foreach>
            GROUP BY shop_id
            </script>
            """)
    List<ShopIdScoreBO> avgScoreByShopIds(@Param("shopIds") Collection<Long> shopIds);

    @Select("""
            SELECT IFNULL(AVG(scores), 5.0) AS score FROM product_comment WHERE deleted = FALSE AND spu_id = #{spuId}
            """)
    float avgScoreBySpuId(@Param("spuId") Long spuId);

    @Select("""
            <script>
            SELECT spu_id, IFNULL(AVG(scores), 5.0) AS score
            FROM product_comment
            WHERE deleted = FALSE AND spu_id IN
            <foreach item="id" collection="spuIds" open="(" separator="," close=")">
                #{id}
            </foreach>
            GROUP BY spu_id
            </script>
            """)
    List<SpuIdScoreBO> avgScoreBySpuIds(@Param("spuIds") Collection<Long> spuIds);

    default ProductEvaluationDO selectLatestCommentBySpuId(Long spuId) {
        CoreLambdaQueryWrapper<ProductEvaluationDO> queryWrapper = new CoreLambdaQueryWrapper<>();
        queryWrapper.eq(ProductEvaluationDO::getSpuId, spuId);
        queryWrapper.orderByDesc(ProductEvaluationDO::getCreateTime);
        queryWrapper.limitN(1);
        return selectOne(queryWrapper);
    }

    default Integer selectCount(Long shopId, ScoreTypeEnum scoreType, @Nullable Boolean hasPictures) {
        CoreLambdaQueryWrapper<ProductEvaluationDO> queryWrapper = new CoreLambdaQueryWrapper<>();
        queryWrapper.eq(ProductEvaluationDO::getVisible, true);
        queryWrapper.eqIfPresent(ProductEvaluationDO::getShopId, shopId);
        if (hasPictures != null) {
            queryWrapper.isNotNull(ProductEvaluationDO::getPicUrls);
        }
        wrapScoreType(queryWrapper, scoreType);
        Long aLong = selectCount(queryWrapper);
        if (aLong == null) {
            return 0;
        }
        return Math.toIntExact(aLong);
    }

    private static void wrapScoreType(CoreLambdaQueryWrapper<ProductEvaluationDO> wrapper, ScoreTypeEnum scoreType) {
        if (scoreType == null) {
            return;
        }
        switch (scoreType) {
            case POSITIVE:
                wrapper.gt(ProductEvaluationDO::getScores, 3);
                break;
            case NEUTER:
                wrapper.eq(ProductEvaluationDO::getScores, 3);
                break;
            case NEGATIVE:
                wrapper.lt(ProductEvaluationDO::getScores, 3);
                break;
            case HAS_PICTURE:
                wrapper.isNotNull(ProductEvaluationDO::getPicUrls);
                break;
            default:
                break;
        }
    }

    @Select("""
            <script>
            SELECT shop_id, COUNT(1) AS comment_count
            FROM product_comment
            WHERE deleted = FALSE AND shop_id IN
            <foreach item="id" collection="shopIds" open="(" separator="," close=")">
                #{id}
            </foreach>
            GROUP BY shop_id
            </script>
            """)
    List<ShopIdCommentCountBO> selectCommentCountByShopIds(@Param("shopIds") Collection<Long> shopIds);

}
